package monsterRun.data;
import java.sql.*;

public class DataConnection {

	/**	
	 * 	This D.A.L Class provides simplified 
	 * 	access to the data stored in the Oracle DB
	 * 	
	 * 	@author s3252820 Gareth Ng 2012
	 */

	private static String username = "s3252820";
	private static String password = "p6345";
	private String url;
	private Connection conn;

	public DataConnection(){
		/* When you create an instance of the DB Connection, it loads the
		** 	1) Oracle DB Driver
		*	2) Connection to the Database using the connection url
		*/
		loadDriver();
		connectToDB();
	}

	public boolean loadDriver(){
		try{
			// Load Oracle JDBC Driver
			Class.forName("oracle.jdbc.driver.OracleDriver");
			url = "jdbc:oracle:thin:@emu.cs.rmit.edu.au:1521:GENERAL";
			return true;
		} catch(ClassNotFoundException ex) {
			System.out.println("Error loading JDBC Driver");
			System.exit(1);
			return false;
		} catch(Exception e){
			System.out.println("Unknown Error");
			e.printStackTrace();
			System.exit(1);
			return false;
		}
	}

	public boolean connectToDB(){
		// Establish connection to the Database
		try{
			conn = DriverManager.getConnection(url, username, password);
			return true;
		}
		catch (SQLException sqle){
			System.out.println("Error connecting to Database");
			return false;
		}
		catch (Exception e){
			System.out.println("Unknown Error");
			e.printStackTrace();
			return false;
		}
	}
	
	public ResultSet runQuery(String query) throws SQLException{
		Statement statement = conn.createStatement();
		ResultSet records = statement.executeQuery(query);
		return records;
	}
	
	public void close() throws SQLException{
		conn.close();
	}

	/**
	 * 	Database Methods Below 
	 * @author s3252820 Gareth Ng 2012
	 */
	
	public boolean login(String username, String password){
		try{
			if (!connectToDB()){
				System.out.println("Error logging player into DB");
				return false;
			} else {
				String query = "SELECT * FROM Player WHERE Username = '" + username + 
				"' AND Pass = '" + password + "'";

				Statement statement = conn.createStatement();
				ResultSet rs = statement.executeQuery(query);
				
				if (!rs.next()) {
				// Player does not exist, create the table
					System.out.println("Player does not exist");
					conn.close();
					return false;
				}
				else {
					System.out.println("Successfully logged in");
					conn.close();
					return true;
				}
			}
		}catch(Exception e){
			System.out.println("Error logging player to the database");
			e.printStackTrace();
			return false;
		}
	}

	public boolean addPlayerInfo(String firstname, String surname, String address, 
			int phone, String username, String password){
		try {
			if (!connectToDB()){
				System.out.println("Error adding player to the database");
				return false;
			} else {
				
				// Check if table exists in DBMD
				DatabaseMetaData dbm = conn.getMetaData();
				ResultSet rs = dbm.getTables(null, null, "PLAYER", null);
				
				Statement statement = conn.createStatement();
				
				if (!rs.next()) {
				// Player Table does not exist, create the table
					String query = "create table PLAYER (" +
							"Firstname VARCHAR2(20), " +
							"Surname VARCHAR2(20), " +
							"Address VARCHAR2(50), " +
							"Phone INT, " +
							"Username VARCHAR2(20) PRIMARY KEY, " +
							"Pass VARCHAR2(20), " +
							"Score INT )";
					statement.executeUpdate(query);
				}

				String query = "insert into PLAYER values('"+ firstname +"','"+ surname +
					"','" + address +"','"+ phone +"','"+ username +"','"+ password +"','"+ 0 +"')";
				statement.executeUpdate(query);
				System.out.println("Successfully added " + username + " to the database");
				conn.close();
				return true;
			}
		}
		catch(SQLException e){
			System.out.println("This player's username already exists in the system");
			return false;
		}
		catch(Exception e){
			System.out.println("Error adding player to the database");
			e.printStackTrace();
			return false;
		}
	}
	
	public void getAllPlayerInfo(){
		try {
			if (connectToDB()){
				Statement statement = conn.createStatement();
				String query = "select * from PLAYER";
				ResultSet rs = statement.executeQuery(query);
				System.out.printf("%-10s%-10s\n", "Player", "Score");
				System.out.printf("%-10s%-10s\n", "------", "-----");
				while(rs.next()){
					//System.out.println(rs.getString("USERNAME") + ", " + rs.getString("SCORE"));
					System.out.printf("%-10s%-10s\n", rs.getString("USERNAME"), rs.getString("SCORE"));
				}
				conn.close();
			}
		} 
		catch (SQLException e) {
			System.out.println("Error acquiring player data");
			e.printStackTrace();
		}
	}
	
	public void removeAllPlayerInfo(){
		try {
			if (connectToDB()){
				Statement statement = conn.createStatement();
				String query = "delete from player";
				statement.executeQuery(query);
				System.out.println("Removed all players from database");
				conn.close();
			}
		} 
		catch (SQLException e) {
			System.out.println("Error deleting all players");
			e.printStackTrace();
		}
	}
	
	public void dropTable(){
		try {
			if (connectToDB()){
				Statement statement = conn.createStatement();
				String query = "drop table player";
				statement.executeUpdate(query);
				System.out.println("Dropping player table, goodbye!");
				conn.close();
			}
		} 
		catch (SQLException e) {
			System.out.println("Error dropping table");
			e.printStackTrace();
		}
	}

	public String getURL(){
		return url;
	}
	
	public Connection getConn(){
		return conn;
	}
}